import warnings
warnings.simplefilter(action='ignore')

import pandas as pd
import numpy as np
from math import *
%matplotlib notebook
import matplotlib.pyplot as plt
import plotly.express as px
import plotly.figure_factory as ff
import scipy.stats as sp
from scipy import interpolate
from scipy.spatial import cKDTree
from datetime import datetime
from statsmodels.tsa.stattools import adfuller
from statsmodels.tsa.seasonal import seasonal_decompose

pd.set_option('display.max_columns', 1000)
pd.set_option("display.max_rows", 3000)

Filtering uninformative columns¶

Using 2018, 2019 accidents report data for the maximum relevance to current time frame, in terms of accident event details.

acc = pd.read_csv('./data/dftRoadSafetyData_Accidents_2018.csv')
ac1 = pd.read_csv('./data/Road Safety Data - Accidents 2019.csv')
acc = pd.concat([acc, ac1])
print(acc.shape)
display(acc.head())

(240171, 32)

The latitude and longitude give accurate location of each accident event and the LSOA- Lower Layer Super Output Area are a geographic hierarchy designed to improve the reporting of small area statistics in England and Wales. LSOAs have an average population of 1500 people or 650 households and consist of constituent Output Areas. Output Areas are a finer division of geographical area and consist of 310 households on an average.
Thus, keeping only LSOA of accident location and (Lat, Long) features for geo-location details of accident events and dropping - (Local_Authority_(District), Local_Authority_(Highway), 1st_Road_Number, 2nd_Road_Number)

Police Force gives information about the region wise police force that was responsible for reporting and investigating the event. Did_Police_Officer_Attend_Scene_of_Accident is a binary valued variable that gives post-event information, which is inconsequential. Thus, dropping these.

Number_of_Vehicles and Number_of_Casualties are features that give information about the specific details of the accident and not about anything that may help establish causality or correlation between the environment and the chances of an accident to occur.

Date, Day_of_Week and Time help find patterns and correlations between specific triggers for accidents such as festive seasons, peak traffic timings at rush hour, day and time of the week that are prone to more accidents due to certain causal factors.

1st_Road_Class, Road_Type, Junction_Detail, Junction_Control, 2nd_Road_Class, Road_Surface_Conditions, Special_Conditions_at_Site, Urban_or_Rural_Area give information about the finer details of the location of accidents that may act in conjunction to help identify patterns and alert the algorithm about potential risks of an accident. For eg: The predictive algorithm may identify that A(M) motorway roads with a roundabout or a staggered junction with damp road surface conditions are more prone to severe accidents than their counterparts.

Similarly, Light_Conditions and Weather_Conditions also provide information about climatic conditions that make certain scenarios more prone to accidents than others.

acc.columns

Index(['Accident_Index', 'Location_Easting_OSGR', 'Location_Northing_OSGR',
       'Longitude', 'Latitude', 'Police_Force', 'Accident_Severity',
       'Number_of_Vehicles', 'Number_of_Casualties', 'Date', 'Day_of_Week',
       'Time', 'Local_Authority_(District)', 'Local_Authority_(Highway)',
       '1st_Road_Class', '1st_Road_Number', 'Road_Type', 'Speed_limit',
       'Junction_Detail', 'Junction_Control', '2nd_Road_Class',
       '2nd_Road_Number', 'Pedestrian_Crossing-Human_Control',
       'Pedestrian_Crossing-Physical_Facilities', 'Light_Conditions',
       'Weather_Conditions', 'Road_Surface_Conditions',
       'Special_Conditions_at_Site', 'Carriageway_Hazards',
       'Urban_or_Rural_Area', 'Did_Police_Officer_Attend_Scene_of_Accident',
       'LSOA_of_Accident_Location'],
      dtype='object')

acc = acc[[
    'Accident_Index', 'LSOA_of_Accident_Location', 'Longitude', 'Latitude',
    'Date', 'Day_of_Week', 'Time', '1st_Road_Class', 'Road_Type',
    'Speed_limit', 'Junction_Detail', 'Junction_Control', '2nd_Road_Class',
    'Pedestrian_Crossing-Human_Control',
    'Pedestrian_Crossing-Physical_Facilities', 'Light_Conditions',
    'Weather_Conditions', 'Road_Surface_Conditions',
    'Special_Conditions_at_Site', 'Carriageway_Hazards', 'Urban_or_Rural_Area',
    'Accident_Severity'
]]

NaN val percentages for filtering

print(acc.shape)
acc.isnull().mean()*100

(240171, 22)

Accident_Index                             0.000000
LSOA_of_Accident_Location                  5.062643
Longitude                                  0.034559
Latitude                                   0.034559
Date                                       0.000000
Day_of_Week                                0.000000
Time                                       0.031644
1st_Road_Class                             0.000000
Road_Type                                  0.000000
Speed_limit                                0.000000
Junction_Detail                            0.000000
Junction_Control                           0.000000
2nd_Road_Class                             0.000000
Pedestrian_Crossing-Human_Control          0.000000
Pedestrian_Crossing-Physical_Facilities    0.000000
Light_Conditions                           0.000000
Weather_Conditions                         0.000000
Road_Surface_Conditions                    0.000000
Special_Conditions_at_Site                 0.000000
Carriageway_Hazards                        0.000000
Urban_or_Rural_Area                        0.000000
Accident_Severity                          0.000000
dtype: float64

#dropping nan vals
acc.dropna(inplace=True)
acc.reset_index(drop=True, inplace=True)
acc.head()

Exploring Features for relevance¶

keys = []
cols = [
    'Day of Week', '1st Road Class', 'Road Type', 'Speed Limit', 'Junction Detail',
    'Junction Control', '2nd Road Class', 'Ped Cross - Human',
    'Ped Cross - Physical', 'Light Conditions',
    'Weather', 'Road Surface',
    'Special Conditions at Site', 'Carriageway Hazards', 'Urban Rural',
    'Accident Severity'
]
for name in cols:
    keys.append(pd.read_excel(f'./data/keys/variable lookup_{name}.xls'))

acc.columns

Index(['Accident_Index', 'LSOA_of_Accident_Location', 'Longitude', 'Latitude',
       'Date', 'Day_of_Week', 'Time', '1st_Road_Class', 'Road_Type',
       'Speed_limit', 'Junction_Detail', 'Junction_Control', '2nd_Road_Class',
       'Pedestrian_Crossing-Human_Control',
       'Pedestrian_Crossing-Physical_Facilities', 'Light_Conditions',
       'Weather_Conditions', 'Road_Surface_Conditions',
       'Special_Conditions_at_Site', 'Carriageway_Hazards',
       'Urban_or_Rural_Area', 'Accident_Severity'],
      dtype='object')

repl_cols = [
    'Day_of_Week', '1st_Road_Class', 'Road_Type', 'Speed_limit', 'Junction_Detail',
    'Junction_Control', '2nd_Road_Class', 'Pedestrian_Crossing-Human_Control',
    'Pedestrian_Crossing-Physical_Facilities', 'Light_Conditions',
    'Weather_Conditions', 'Road_Surface_Conditions',
    'Special_Conditions_at_Site', 'Carriageway_Hazards', 'Urban_or_Rural_Area',
    'Accident_Severity'
]
for i in range(len(repl_cols)):
    acc = acc.replace({
        repl_cols[i]:
        dict([(i, a) for i, a in zip(keys[i]['code'], keys[i]['label'])])
    })

acc.head()

for col in acc.columns[7:]:
    print(col, '\n', acc[col].value_counts(), '\n')

1st_Road_Class 
 A               100283
Unclassified     79837
B                27024
C                12699
Motorway          7474
A(M)               624
Name: 1st_Road_Class, dtype: int64 

Road_Type 
 Single carriageway    164413
Dual carriageway       36118
Roundabout             13811
One way street          5844
Unknown                 4446
Slip road               3309
Name: Road_Type, dtype: int64 

Speed_limit 
 30 MPH                          136912
60 MPH                           26687
20 MPH                           21263
40 MPH                           19540
70 MPH                           14154
50 MPH                            9306
Data missing or out of range        79
Name: Speed_limit, dtype: int64 

Junction_Detail 
 Not at junction or within 20 metres    95101
T or staggered junction                66628
Crossroads                             21643
Roundabout                             18296
Other junction                          9567
Private drive or entrance               5283
Mini-roundabout                         3176
Slip road                               3158
More than 4 arms (not roundabout)       2926
Data missing or out of range            2163
Name: Junction_Detail, dtype: int64 

Junction_Control 
 Data missing or out of range           101221
Give way or uncontrolled                99142
Auto traffic signal                     25323
Stop sign                                1446
Authorised person                         805
Not at junction or within 20 metres         4
Name: Junction_Control, dtype: int64 

2nd_Road_Class 
 -1              95280
Unclassified    91100
A               23710
B                8887
C                7736
Motorway         1093
A(M)              135
Name: 2nd_Road_Class, dtype: int64 

Pedestrian_Crossing-Human_Control 
 None within 50 metres                 218087
Data missing or out of range            6783
Control by other authorised person      2239
Control by school crossing patrol        832
Name: Pedestrian_Crossing-Human_Control, dtype: int64 

Pedestrian_Crossing-Physical_Facilities 
 No physical crossing facilities within 50 metres                             174985
Pedestrian phase at traffic signal junction                                   18356
Pelican, puffin, toucan or similar non-junction pedestrian light crossing     12964
Zebra                                                                          9191
Data missing or out of range                                                   6074
Central refuge                                                                 5862
Footbridge or subway                                                            509
Name: Pedestrian_Crossing-Physical_Facilities, dtype: int64 

Light_Conditions 
 Daylight                       162950
Darkness - lights lit           47075
Darkness - no lighting          11227
Darkness - lighting unknown      5025
Darkness - lights unlit          1664
Name: Light_Conditions, dtype: int64 

Weather_Conditions 
 Fine no high winds       182273
Raining no high winds     26294
Unknown                    6883
Other                      5429
Raining + high winds       2443
Fine + high winds          2186
Snowing no high winds      1244
Fog or mist                 792
Snowing + high winds        397
Name: Weather_Conditions, dtype: int64 

Road_Surface_Conditions 
 Dry                             165209
Wet or damp                      56469
Data missing or out of range      2542
Frost or ice                      2290
Snow                              1161
Flood over 3cm. deep               270
Name: Road_Surface_Conditions, dtype: int64 

Special_Conditions_at_Site 
 None                                          219760
Data missing or out of range                    3332
Roadworks                                       2541
Mud                                              543
Auto traffic signal - out                        534
Road surface defective                           481
Road sign or marking defective or obscured       360
Oil or diesel                                    314
Auto signal part defective                        76
Name: Special_Conditions_at_Site, dtype: int64 

Carriageway_Hazards 
 None                                               221125
Data missing or out of range                         2871
Other object on road                                 1976
Any animal in carriageway (except ridden horse)       640
Vehicle load on road                                  515
Pedestrian in carriageway - not injured               426
Previous accident                                     388
Name: Carriageway_Hazards, dtype: int64 

Urban_or_Rural_Area 
 Urban    154682
Rural     73259
Name: Urban_or_Rural_Area, dtype: int64 

Accident_Severity 
 Slight     181445
Serious     43478
Fatal        3018
Name: Accident_Severity, dtype: int64

# Majorly single valued thus uninformative 
acc = acc.drop(columns=[
    'Junction_Control', '2nd_Road_Class', 'Pedestrian_Crossing-Human_Control',
    'Pedestrian_Crossing-Physical_Facilities', 'Special_Conditions_at_Site',
    'Carriageway_Hazards'
])

# Adding unknown to mode of data of R.V.
acc = acc.replace({
    '1st_Road_Class': {
        'A(M)': 'Motorway'
    },
    'Road_Type': {
        'Unknown': 'Single carriageway'
    },
    'Speed_limit': {
        'Data missing or out of range': '30 MPH'
    },
    'Junction_Detail': {
        'Data missing or out of range': 'Not at junction or within 20 metres'
    },
    'Weather_Conditions': {
        'Unknown': 'Fine no high winds'
    },
    'Road_Surface_Conditions': {
        'Data missing or out of range': 'Dry'
    }    
})

Road Details¶

In order to understand and investigate the correlation between the Roads-related factors and Accident severity, the classes, types and conditions of roads are studied in conjunction with the severity of the events.

def P(acc, col, cond):
    return pd.DataFrame(
    acc.groupby(cond)[col].value_counts(
        normalize=True).unstack()).reset_index()

def chart_it(prob):
    df = prob.melt(id_vars = prob.columns[0], value_vars = prob.columns[1:])
    fig = px.bar(df, x=prob.columns[0], y="value",
                 color=prob.columns.name, barmode='group',
                 height=400)
    fig.show()

Road Class¶

acc['1st_Road_Class'].value_counts()

A               100283
Unclassified     79837
B                27024
C                12699
Motorway          8098
Name: 1st_Road_Class, dtype: int64

road = P(acc, 'Accident_Severity', '1st_Road_Class')
road

chart_it(road)

road = P(acc, '1st_Road_Class', 'Accident_Severity')
road

chart_it(road)

In order to comment on the risk of traveling on a particular road, consider the P(Accident_Severity/1st_Road_Class) - Probability of Accident Severity given a Road Class, which highlights the investigative nature of the study by pointing towards the probability of the effect, given the cause. Thus, Motorways are the most prone to fatalities (2.1%) as compared to its counterparts.
Fatal accidents occur in greater frequency on A roads.

On the other hand, after a Bayesian treatment of the value counts of the same random variables, it is interesting to notice that the probabilities of P(1st_Road_Class/Accident_Severity) indicating probability of cause given effect turn out to be in the same proportion. This self-balancing is due to the extreme imbalance in the value counts of the Accident_Severity random variable. Even after random sampling to get equal number of Road class values, the extreme imbalance in Fatal, Severe and Slight variables causes the probabilities to skew in the direction of Slight accidents, irrespective of the feature variable. This must be handled at the time of model training.

l = []; equi = pd.DataFrame()
for val in (acc['1st_Road_Class'].unique()):
    a = acc[acc['1st_Road_Class'] == val]
    a = a.sample(n=1000)
    l.append(a)
equi = l[0]
for df in l[1:]:
    equi = pd.concat([equi, df])

equi['1st_Road_Class'].value_counts()

A               1000
B               1000
Unclassified    1000
C               1000
Motorway        1000
Name: 1st_Road_Class, dtype: int64

road = P(equi, 'Accident_Severity', '1st_Road_Class')
road

road = P(equi, '1st_Road_Class', 'Accident_Severity')
road

Road Type¶

acc['Road_Type'].value_counts()

Single carriageway    168859
Dual carriageway       36118
Roundabout             13811
One way street          5844
Slip road               3309
Name: Road_Type, dtype: int64

rtype = P(acc, 'Accident_Severity', 'Road_Type')
rtype

rtype = P(acc, 'Road_Type', 'Accident_Severity')
rtype

Dual carriageways are more prone to fatalities than its counterparts (17%).

Speed Limit¶

speed = P(acc, 'Accident_Severity', 'Speed_limit')
speed

speed_hist = pd.DataFrame(acc['Speed_limit'].value_counts()).reset_index()
speed_hist.sort_values(by=['index'], inplace=True)
fig = px.histogram(speed_hist, x='index', y='Speed_limit')
fig.show()

Notice that 60MPH speed limit roads have higher probability of fatal accidents, even though 30MPH roads have the highest frequency of accidents. This can be explained by the safe assumption that the average vehicle speeds have a direct positive correlation with the speed limit of the road. But 70MPH roads have lesser probability of fatal accidents and a significant jump in probability of slight accidents, maybe due to the fact that 70MPH roads are particularly built and engineered in a way that makes driving at high speed a more comfortable and safer experience.

Junction Details¶

junc = P(acc, 'Accident_Severity', 'Junction_Detail')
junc

junc = P(acc, 'Junction_Detail', 'Accident_Severity')
display(junc)
chart_it(junc)

Clearly, roads having no junction within 20 meters have the most number of accidents and are the riskiest too, with respect to the probability of having a fatality. The probability of a slight accident on these kind of roads is the least when compared to its counterparts too.

Climate and Weather¶

light = P(acc, 'Accident_Severity', 'Light_Conditions')
light

weather = P(acc, 'Accident_Severity', 'Weather_Conditions')
weather

For obvious reasons, conditions with No lighting and climatic conditions of high winds and rains have the highest probability of fatalities and the lowest probabilities of slight accidents

Time Series Analysis¶

time = acc[['Date', 'Accident_Severity']]
time['Date'] = time['Date'].apply(lambda x: datetime.strptime(x, '%d/%m/%Y'))

time = pd.DataFrame(time.groupby('Date')['Accident_Severity'].value_counts()).unstack().unstack()
time = pd.DataFrame(time['Accident_Severity']).reset_index()
time.replace(np.nan, 0, inplace=True)
time

def test_stationarity(df):

    #Compute Rolling Stats:
    rolling_mean = df.rolling(12).mean()
    rolling_std = df.rolling(12).std()

    #Plotting Rolling Stats:
    rolling_mean['id'] = 'rolling-mean'
    rolling_std['id'] = 'rolling-std'
    df['id'] = 'original'
    plotting = pd.concat([df, rolling_mean, rolling_std])
    plotting.columns = ['value', 'id']
    plotting['date'] = plotting.index
    fig = px.line(plotting,
                  x='date',
                  y='value',
                  line_group='id',
                  hover_name='id',
                  color='id',
                  title='Viz for Rolling Stats')
    fig.update_xaxes(
        rangeslider_visible=True,
        rangeselector=dict(
            buttons=list([
                dict(count=1, label="1m", step="month", stepmode="backward"),
                dict(count=6, label="6m", step="month", stepmode="backward"),
                dict(count=1, label="YTD", step="year", stepmode="todate"),
                dict(count=1, label="1y", step="year", stepmode="backward"),
                dict(step="all")
            ])
        )
    )
    fig.show()
    
    #Dickey-Fuller Test:
    print('ADFuller Test Results-', '\n')
    df = df.drop(columns=['id'])
    dftest = adfuller(df.iloc[:,0], autolag='AIC')
    dfoutput = pd.Series(dftest[0:4], index=['Test Statistic','p-value','#Lags Used','Number of Observations Used'])
    for key,value in dftest[4].items():
        dfoutput['Critical Value (%s)'%key] = value
    print(dfoutput)

test_stationarity(time[time['Accident_Severity']=='Fatal'][['Date', 0]].set_index('Date'))
test_stationarity(time[time['Accident_Severity']=='Serious'][['Date', 0]].set_index('Date'))
test_stationarity(time[time['Accident_Severity']=='Slight'][['Date', 0]].set_index('Date'))

ADFuller Test Results- 

Test Statistic                -9.186216e+00
p-value                        2.184083e-15
#Lags Used                     5.000000e+00
Number of Observations Used    7.240000e+02
Critical Value (1%)           -3.439414e+00
Critical Value (5%)           -2.865540e+00
Critical Value (10%)          -2.568900e+00
dtype: float64

ADFuller Test Results- 

Test Statistic                  -4.012797
p-value                          0.001345
#Lags Used                      14.000000
Number of Observations Used    715.000000
Critical Value (1%)             -3.439529
Critical Value (5%)             -2.865591
Critical Value (10%)            -2.568927
dtype: float64

ADFuller Test Results- 

Test Statistic                  -3.541004
p-value                          0.006994
#Lags Used                      20.000000
Number of Observations Used    709.000000
Critical Value (1%)             -3.439607
Critical Value (5%)             -2.865625
Critical Value (10%)            -2.568945
dtype: float64

total = time[time['Accident_Severity'] == 'Slight'][['Date',
                                                     0]].set_index('Date')
total[0] = total[0] + time[time['Accident_Severity'] == 'Fatal'][[
    'Date', 0
]].set_index('Date')[0] + time[time['Accident_Severity'] == 'Serious'][[
    'Date', 0
]].set_index('Date')[0]

test_stationarity(total)

ADFuller Test Results- 

Test Statistic                  -3.558884
p-value                          0.006598
#Lags Used                      20.000000
Number of Observations Used    709.000000
Critical Value (1%)             -3.439607
Critical Value (5%)             -2.865625
Critical Value (10%)            -2.568945
dtype: float64

total.reset_index(inplace=True)
total = total.resample('M', on='Date').sum()
decomposition = seasonal_decompose(total)

trend = decomposition.trend
seasonal = decomposition.seasonal
residual = decomposition.resid

plt.subplot(411)
plt.plot(total, label='Original')
plt.legend(loc='best')
plt.subplot(412)
plt.plot(trend, label='Trend')
plt.legend(loc='best')
plt.subplot(413)
plt.plot(seasonal,label='Seasonality')
plt.legend(loc='best')
plt.subplot(414)
plt.plot(residual, label='Residuals')
plt.legend(loc='best')
plt.tight_layout()

#px.line(seasonal, x=seasonal.index , y=0)

It can be noted from the rolling statistics graph for daily fatalities that there's a sudden increase in the number of deaths during New-Year's time as compared to other months. But this trend is quite the opposite for serious and slight accidents. This could be due to high number of drunk-driving cases on new-years. Looking at the total accident cases' rolling statistics show that the total accident cases drop the most during new-years but peak during the month of July 2018. There is also an indication of a rough trend of seasonality in the daily accident cases.
The Dickey-Fuller test of stationarity gives the result that the time series is stationary as the p-value < 0.05 and Test-statistic < Critical value at 5%.
But, this is the result of conducting the test on data that has a time frequency of 1 day. Due to this high frequency dataset, the noise increases and that results in an overall average of constant trend.
Thus, upon further investigation of monthly data, it is clear that there is a negative trend in the total number of accidents from 2018 to 2020 and a clear seasonal pattern can be detected. The cases rise sharply during the ending months of summer in July and August, then fall suddenly during September and shoot up to a maximum towards the end of the year in November and December. This repeated pattern (observed over the course of 2 years) has a direct correlation with the festive seasons.

day = P(acc, 'Accident_Severity', 'Day_of_Week')
day

time1 = pd.DataFrame(acc.groupby('Time')['Accident_Severity'].value_counts()).unstack().unstack()
time1 = pd.DataFrame(time1['Accident_Severity']).reset_index()
time1.replace(np.nan, 0, inplace=True)
total1 = time1[time1['Accident_Severity'] == 'Slight'][['Time',
                                                     0]].set_index('Time')
total[0] = total[0] + time1[time1['Accident_Severity'] == 'Fatal'][[
    'Time', 0
]].set_index('Time')[0] + time1[time1['Accident_Severity'] == 'Serious'][[
    'Time', 0
]].set_index('Time')[0]

test_stationarity(total1)

ADFuller Test Results- 

Test Statistic                   -1.525997
p-value                           0.520614
#Lags Used                       24.000000
Number of Observations Used    1414.000000
Critical Value (1%)              -3.434983
Critical Value (5%)              -2.863586
Critical Value (10%)             -2.567859
dtype: float64

Saturday and Sundays have a peak probability of Fatal accidents due to the fact that there are more vehicles on the road on the weekend since its a holiday. Looking at the time of the day for accidents, there are 2 sharp peaks in the accident rates, at around 8:30 AM and in the evening between 4-7 PM. This makes sense since these timings during the day are rush-hours and increase the probability of an accident.

Analyzing Traffic Data¶

Since the event of an accident is not only dependent on external environmental factors, but also on the other vehicles present on the road during the event, incorporating relevant traffic data would improve the predictive power of the dataset.

Using Hourly Traffic Data¶

traffic = pd.read_csv('./data/dft_traffic_counts_raw_counts.csv')
traffic.head(50)

traffic.year.value_counts()

2019    301944
2018    289848
2017    186468
2016    166824
2015    103491
Name: year, dtype: int64

Since the study only involves 2018 and 2019, drop the previous years

traffic = traffic[(traffic['year']==2018) | (traffic['year']==2019)]

traffic.shape

(591792, 32)

Computing the mean number of vehicle_types for each count_point_id and hour of the day

traffic['all_gvs'] = traffic['all_hgvs']+traffic['lgvs']+traffic1['buses_and_coaches']

traffic1 = pd.DataFrame(
    traffic.groupby(['count_point_id',
                     'hour', 'latitude', 'longitude', 'year'])['link_length_km', 'pedal_cycles',
                              'two_wheeled_motor_vehicles', 'cars_and_taxis',
                              'all_gvs'].sum()).reset_index()
traffic1 = pd.DataFrame(
    traffic1.groupby(['count_point_id',
                     'hour', 'latitude', 'longitude'])['link_length_km', 'pedal_cycles',
                              'two_wheeled_motor_vehicles', 'cars_and_taxis',
                              'all_gvs'].mean()).reset_index()
traffic1

traffic1.shape

(251352, 9)

del traffic

traffic1.hour.value_counts()

18    20946
17    20946
16    20946
15    20946
14    20946
13    20946
12    20946
11    20946
10    20946
9     20946
8     20946
7     20946
Name: hour, dtype: int64

def plot(col):
    tplot = pd.DataFrame(traffic1.groupby('hour')[col].mean()).reset_index()
    fig = px.line(tplot, x='hour', y=col)
    fig.show()

plot('cars_and_taxis')

tplot = pd.DataFrame(traffic1.groupby('hour')['cars_and_taxis'].mean()).reset_index()
tplot['hr_sin'] = tplot.hour*(2.*np.pi/24)
x = tplot['hr_sin']
y = tplot['cars_and_taxis']
f = interpolate.interp1d(x, y, kind='nearest', fill_value = 'extrapolate')

xnew = np.append(np.array(tplot['hour']), [0,1,2,3,4,5,6,19,20,21,22,23])
xnew = xnew*(2.*np.pi/24)
ynew = f(xnew)
plt.plot(x, y, 'o', xnew, ynew, '-')
plt.show()

Since every count_point_id has vehicle counts for only the major hours of the day when the traffic is at its peak (0007 - 1800), the method of cubic spline interpolation is tried in order to compute the missing values for hours between 0000-0007 and 1800-0000. Since this must be done for each and every count_point_id individually since each of them have varying traffic counts, there are extremely less datums per counting point for efficient extrapolation of the function on the uncovered hours. Thus, instead of using raw traffic data per hour, AADF traffic data is used for the years 2018-2019

del traffic1

Using AADF Traffic Data¶

The annual average daily flow (AADF) of traffic is measured at various points on major and minor roads across the UK, according to the type of vehicle.

traffic = pd.read_csv('./data/dft_traffic_counts_aadf.csv')
traffic = traffic[(traffic['year']==2018) | (traffic['year']==2019)]
traffic = traffic[[
    'count_point_id', 'road_name', 'latitude', 'longitude', 'pedal_cycles',
    'two_wheeled_motor_vehicles', 'cars_and_taxis', 'buses_and_coaches', 'lgvs', 'all_hgvs'
]]
traffic = pd.DataFrame(traffic.groupby('count_point_id').mean()).reset_index()
traffic.head()

Using Population Density Statistics¶

The population density of an area (LSOA) indicates how crowded a region is and thus adds on to the data that indicates the intensity of activity in each region. This helps understand the factors that contribute to the event of an accident and may thus improve the predictive power of the Classifier.

The population counts of all areas of UK have been used from UK's most recent national Census of 2011.

population = pd.read_csv('./data/census_2011.csv')
population.head()

population = population[['geography code', 'Variable: Density (number of persons per hectare); measures: Value']]
population.columns = ['LSOA', 'pop_density']
population.head()

Merging and Preparing Dataset¶

Merging Population Density data with accident data using LSOA

data = pd.merge(left=acc, right=population, how='left', left_on='LSOA_of_Accident_Location', right_on='LSOA')
data

data.shape

(227941, 18)

In order to merge traffic value counts with the accident data, there is no common key value such as LSOA. The keys for traffic counts are the count_point_ids that indicate the roads where the counts were taken. Thus, in order to add traffic data to the accident events, the (latitude, longitude) coordinates are used. By comparing the nearest/closest coordinates of traffic count_point_ids and the accident events, the traffic counts can be added to each accident event.

A simple brute force search for the nearest neighbors amongst the two datasets would take an immense amount of time due to the large volume of data. Thus, the KDTree algorithm (using the sliding midpoint rule), as described in Maneewongvatana and Mount 1999, is used in order to efficiently calculate the nearest neighbor for each coordinate across datasets.

tree = cKDTree(traffic[['longitude', 'latitude']])
d,i = tree.query(data[['Longitude', 'Latitude']])
traffic = traffic.loc[i]
traffic.reset_index(inplace=True, drop=True)
data = pd.concat([data, traffic], axis=1)
data

data.shape

(227941, 27)

Now that the datasets have been merged, looking at the distances between coordinates from each dataset that have been picked as nearest neighbors

# Haversine Formula for computing distances between coordinates
def distance(lat1, lon1, lat2, lon2):
    p = 0.017453292519943295
    a = 0.5 - cos(
        (lat2 - lat1) * p) / 2 + cos(lat1 * p) * cos(lat2 * p) * (1 - cos(
            (lon2 - lon1) * p)) / 2
    return (12742 * asin(sqrt(a))) * 1000

data['distance'] = data.apply(lambda x: distance(x.latitude, x.longitude, x.Latitude, x.Longitude), axis=1)

data['distance'].describe()

count    227941.000000
mean        590.496963
std         673.412777
min           0.000000
25%         194.007850
50%         385.870443
75%         711.834689
max       16284.173724
Name: distance, dtype: float64

The statistics of the distances between the 2 set of coordinates show that a majority of the distances are under 711 meters. Thus, the count points have more or less been successfully merged according to the coordinates. The max distance is 16kms and that may be due to the unavailability of a counting point near the accident location due to it being a non-major road in a rural area.

data.columns

Index(['Accident_Index', 'LSOA_of_Accident_Location', 'Longitude', 'Latitude',
       'Date', 'Day_of_Week', 'Time', '1st_Road_Class', 'Road_Type',
       'Speed_limit', 'Junction_Detail', 'Light_Conditions',
       'Weather_Conditions', 'Road_Surface_Conditions', 'Urban_or_Rural_Area',
       'Accident_Severity', 'LSOA', 'pop_density', 'count_point_id',
       'latitude', 'longitude', 'pedal_cycles', 'two_wheeled_motor_vehicles',
       'cars_and_taxis', 'buses_and_coaches', 'lgvs', 'all_hgvs', 'distance'],
      dtype='object')

data = data[['Accident_Index', 'Longitude', 'Latitude',
       'Date', 'Day_of_Week', 'Time', '1st_Road_Class', 'Road_Type',
       'Speed_limit', 'Junction_Detail', 'Light_Conditions',
       'Weather_Conditions', 'Road_Surface_Conditions', 'Urban_or_Rural_Area',
       'Accident_Severity', 'pop_density', 'count_point_id',
       'pedal_cycles', 'two_wheeled_motor_vehicles',
       'cars_and_taxis', 'buses_and_coaches', 'lgvs', 'all_hgvs']]

Time stamp based categorical features have been converted to cyclic - sinusoidal based continuous valued variables. This is to emphasize on the cyclic nature of time during the day, days of the week and the months of the year. This helps establish numeric relations within the feature and add more context to the judgement of the predictor.

repl = pd.read_excel(f'./data/keys/variable lookup_Day of Week.xls')
data = data.replace({
    'Day_of_Week':
    dict([(i, a) for i, a in zip(repl['label'], repl['code'])])
})

def date_to_sin(s):
    d = {0:0, 1:31, 2:30, 3:31, 4:30, 5:31, 6:30, 7:31, 8:31, 9:30, 10:31, 11:30, 12:31}
    day = int(s[:2]) + np.sum([d[i] for i in range(int(s[3:5]))])
    return sin(day*2*np.pi/365)

data['Time'] = data['Time'].apply(lambda x: sin((3600*int(x[:2]) + 60*int(x[3:]))*2*np.pi/(24*3600)))
data['Day_of_Week'] = data['Day_of_Week'].apply(lambda x: sin(x*2*np.pi/7))
data['Date'] = data['Date'].apply(lambda x: date_to_sin(x))

data

data.to_pickle('./data/struct_dataset.pkl')

	Accident_Index	Location_Easting_OSGR	Location_Northing_OSGR	Longitude	Latitude	Police_Force	Accident_Severity	Number_of_Vehicles	Number_of_Casualties	Date	Day_of_Week	Time	Local_Authority_(District)	Local_Authority_(Highway)	1st_Road_Class	1st_Road_Number	Road_Type	Speed_limit	Junction_Detail	Junction_Control	2nd_Road_Class	2nd_Road_Number	Pedestrian_Crossing-Physical_Facilities	Light_Conditions	Weather_Conditions	Road_Surface_Conditions	Urban_or_Rural_Area	Did_Police_Officer_Attend_Scene_of_Accident	LSOA_of_Accident_Location
0	2018010080971	529150.0	182270.0	-0.139737	51.524587	1	3	2	2	01/01/2018	2	01:30	2	E09000007	3	501	3	30	0	-1	-1	0	0	4	1	1	1	1	E01000854
1	2018010080973	542020.0	184290.0	0.046471	51.539651	1	3	1	1	01/01/2018	2	00:50	17	E09000025	4	165	6	30	2	4	6	0	0	4	1	1	1	1	E01003531
2	2018010080974	531720.0	182910.0	-0.102474	51.529746	1	3	2	1	01/01/2018	2	00:45	3	E09000019	3	1	6	20	6	4	5	0	5	4	1	1	1	1	E01002723
3	2018010080981	541450.0	183220.0	0.037828	51.530179	1	2	2	1	01/01/2018	2	03:00	17	E09000025	4	167	3	30	7	2	3	124	5	4	2	2	1	1	E01003492
4	2018010080982	543580.0	176500.0	0.065781	51.469258	1	2	2	2	01/01/2018	2	02:20	6	E09000011	3	207	6	30	0	-1	-1	0	0	4	1	2	1	1	E01001682

Accident_Severity	1st_Road_Class	Fatal	Serious	Slight
0	A	0.016473	0.189484	0.794043
1	B	0.014543	0.209702	0.775755
2	C	0.010395	0.177416	0.812190
3	Motorway	0.021363	0.153495	0.825142
4	Unclassified	0.008367	0.191803	0.799830

1st_Road_Class	Accident_Severity	A	B	C	Motorway	Unclassified
0	Fatal	0.547382	0.130219	0.043738	0.057323	0.221339
1	Serious	0.437049	0.130342	0.051819	0.028589	0.352201
2	Slight	0.438860	0.115539	0.056844	0.036827	0.351930

Accident_Severity	1st_Road_Class	Fatal	Serious	Slight
0	A	0.008	0.190	0.802
1	B	0.016	0.216	0.768
2	C	0.013	0.177	0.810
3	Motorway	0.021	0.148	0.831
4	Unclassified	0.009	0.185	0.806

1st_Road_Class	Accident_Severity	A	B	C	Motorway	Unclassified
0	Fatal	0.119403	0.238806	0.194030	0.313433	0.134328
1	Serious	0.207424	0.235808	0.193231	0.161572	0.201965
2	Slight	0.199651	0.191187	0.201643	0.206871	0.200647

Accident_Severity	Road_Type	Fatal	Serious	Slight
0	Dual carriageway	0.017360	0.173653	0.808987
1	One way street	0.006160	0.156742	0.837098
2	Roundabout	0.003403	0.134748	0.861849
3	Single carriageway	0.013455	0.201156	0.785389
4	Slip road	0.010879	0.139619	0.849501

Road_Type	Accident_Severity	Dual carriageway	One way street	Roundabout	Single carriageway	Slip road
0	Fatal	0.207753	0.011928	0.015573	0.752816	0.011928
1	Serious	0.144257	0.021068	0.042803	0.781246	0.010626
2	Slight	0.161035	0.026961	0.065601	0.730910	0.015492

Accident_Severity	Speed_limit	Fatal	Serious	Slight
0	20 MPH	0.004374	0.157080	0.838546
1	30 MPH	0.007643	0.180092	0.812265
2	40 MPH	0.017042	0.207523	0.775435
3	50 MPH	0.026220	0.205459	0.768322
4	60 MPH	0.034511	0.261401	0.704088
5	70 MPH	0.026848	0.178324	0.794828

Accident_Severity	Junction_Detail	Fatal	Serious	Slight
0	Crossroads	0.008271	0.173312	0.818417
1	Mini-roundabout	0.002834	0.143577	0.853589
2	More than 4 arms (not roundabout)	0.005810	0.184211	0.809979
3	Not at junction or within 20 metres	0.019452	0.205194	0.775354
4	Other junction	0.010348	0.196822	0.792830
5	Private drive or entrance	0.008896	0.191936	0.799167
6	Roundabout	0.004974	0.138883	0.856143
7	Slip road	0.014883	0.151045	0.834072
8	T or staggered junction	0.009561	0.192997	0.797443

Junction_Detail	Accident_Severity	Crossroads	Mini-roundabout	More than 4 arms (not roundabout)	Not at junction or within 20 metres	Other junction	Private drive or entrance	Roundabout	Slip road	T or staggered junction
0	Fatal	0.059311	0.002982	0.005633	0.626905	0.032803	0.015573	0.030152	0.015573	0.211067
1	Serious	0.086274	0.010488	0.012397	0.459037	0.043309	0.023322	0.058443	0.010971	0.295759
2	Slight	0.097622	0.014941	0.013062	0.415630	0.041803	0.023269	0.086329	0.014517	0.292827

Accident_Severity	Light_Conditions	Fatal	Serious	Slight
0	Darkness - lighting unknown	0.010945	0.140100	0.848955
1	Darkness - lights lit	0.014041	0.202953	0.783006
2	Darkness - lights unlit	0.024639	0.213942	0.761418
3	Darkness - no lighting	0.046851	0.266144	0.687005
4	Daylight	0.010647	0.183345	0.806008

Accident_Severity	Weather_Conditions	Fatal	Serious	Slight
0	Fine + high winds	0.023788	0.209058	0.767155
1	Fine no high winds	0.013259	0.192777	0.793964
2	Fog or mist	0.049242	0.202020	0.748737
3	Other	0.007552	0.147909	0.844539
4	Raining + high winds	0.023741	0.212034	0.764224
5	Raining no high winds	0.011447	0.183806	0.804746
6	Snowing + high winds	0.005038	0.148615	0.846348
7	Snowing no high winds	0.013666	0.147106	0.839228

Accident_Severity	Day_of_Week	Fatal	Serious	Slight
0	Friday	0.011830	0.185368	0.802802
1	Monday	0.013337	0.183867	0.802796
2	Saturday	0.016344	0.201794	0.781862
3	Sunday	0.017630	0.214514	0.767856
4	Thursday	0.011540	0.187545	0.800915
5	Tuesday	0.012207	0.183989	0.803804
6	Wednesday	0.011461	0.185758	0.802781

	count_point_id	direction_of_travel	year	count_date	hour	region_id	region_name	local_authority_id	local_authority_name	road_name	road_type	start_junction_road_name	end_junction_road_name	easting	northing	latitude	longitude	link_length_km	link_length_miles	pedal_cycles	two_wheeled_motor_vehicles	cars_and_taxis	buses_and_coaches	lgvs	hgvs_2_rigid_axle	hgvs_3_rigid_axle	hgvs_4_or_more_rigid_axle	hgvs_3_or_4_articulated_axle	hgvs_5_articulated_axle	hgvs_6_articulated_axle	all_hgvs	all_motor_vehicles
0	52	E	2019	06-09-2019	17	1	South West	1	Isles of Scilly	A3112	Major	A3111	A3110	91203	10217	49.912239	-6.302848	2.0	1.24	6	1	16	0	8	0	0	0	0	0	0	0	25
1	52	E	2019	06-09-2019	12	1	South West	1	Isles of Scilly	A3112	Major	A3111	A3110	91203	10217	49.912239	-6.302848	2.0	1.24	9	1	28	0	8	0	0	0	0	0	0	0	37
2	52	E	2019	06-09-2019	14	1	South West	1	Isles of Scilly	A3112	Major	A3111	A3110	91203	10217	49.912239	-6.302848	2.0	1.24	7	0	18	0	9	0	0	0	0	0	0	0	27
3	52	W	2019	06-09-2019	14	1	South West	1	Isles of Scilly	A3112	Major	A3111	A3110	91203	10217	49.912239	-6.302848	2.0	1.24	3	1	20	1	11	0	0	0	0	0	0	0	33
4	52	W	2019	06-09-2019	12	1	South West	1	Isles of Scilly	A3112	Major	A3111	A3110	91203	10217	49.912239	-6.302848	2.0	1.24	3	0	19	1	9	0	0	0	0	0	0	0	29
5	52	W	2019	06-09-2019	17	1	South West	1	Isles of Scilly	A3112	Major	A3111	A3110	91203	10217	49.912239	-6.302848	2.0	1.24	4	2	8	1	12	0	0	0	0	0	0	0	23
6	52	W	2019	06-09-2019	18	1	South West	1	Isles of Scilly	A3112	Major	A3111	A3110	91203	10217	49.912239	-6.302848	2.0	1.24	4	0	13	0	6	0	0	0	0	0	0	0	19
7	52	E	2019	06-09-2019	9	1	South West	1	Isles of Scilly	A3112	Major	A3111	A3110	91203	10217	49.912239	-6.302848	2.0	1.24	4	0	19	0	12	1	0	0	0	0	0	1	32
8	52	W	2019	06-09-2019	10	1	South West	1	Isles of Scilly	A3112	Major	A3111	A3110	91203	10217	49.912239	-6.302848	2.0	1.24	3	1	24	1	12	0	0	0	0	0	0	0	38
9	52	E	2019	06-09-2019	8	1	South West	1	Isles of Scilly	A3112	Major	A3111	A3110	91203	10217	49.912239	-6.302848	2.0	1.24	4	1	9	0	7	0	0	0	0	0	0	0	17
10	52	E	2019	06-09-2019	18	1	South West	1	Isles of Scilly	A3112	Major	A3111	A3110	91203	10217	49.912239	-6.302848	2.0	1.24	0	1	10	0	3	0	0	0	0	0	0	0	14
11	52	W	2019	06-09-2019	15	1	South West	1	Isles of Scilly	A3112	Major	A3111	A3110	91203	10217	49.912239	-6.302848	2.0	1.24	5	1	27	1	22	0	0	0	0	0	0	0	51
12	52	E	2019	06-09-2019	13	1	South West	1	Isles of Scilly	A3112	Major	A3111	A3110	91203	10217	49.912239	-6.302848	2.0	1.24	8	0	22	0	6	0	0	0	0	0	0	0	28
13	52	E	2019	06-09-2019	10	1	South West	1	Isles of Scilly	A3112	Major	A3111	A3110	91203	10217	49.912239	-6.302848	2.0	1.24	7	1	20	0	7	2	0	0	0	0	0	2	30
14	52	W	2019	06-09-2019	16	1	South West	1	Isles of Scilly	A3112	Major	A3111	A3110	91203	10217	49.912239	-6.302848	2.0	1.24	6	2	17	1	16	0	0	0	0	0	0	0	36
15	52	E	2019	06-09-2019	11	1	South West	1	Isles of Scilly	A3112	Major	A3111	A3110	91203	10217	49.912239	-6.302848	2.0	1.24	8	2	14	0	7	0	0	0	0	0	0	0	23
16	52	E	2019	06-09-2019	7	1	South West	1	Isles of Scilly	A3112	Major	A3111	A3110	91203	10217	49.912239	-6.302848	2.0	1.24	3	1	4	0	4	0	0	0	0	0	0	0	9
17	52	W	2019	06-09-2019	7	1	South West	1	Isles of Scilly	A3112	Major	A3111	A3110	91203	10217	49.912239	-6.302848	2.0	1.24	4	0	10	0	6	0	0	0	0	0	0	0	16
18	52	W	2019	06-09-2019	8	1	South West	1	Isles of Scilly	A3112	Major	A3111	A3110	91203	10217	49.912239	-6.302848	2.0	1.24	41	2	33	0	10	0	0	0	0	0	0	0	45
19	52	W	2019	06-09-2019	13	1	South West	1	Isles of Scilly	A3112	Major	A3111	A3110	91203	10217	49.912239	-6.302848	2.0	1.24	3	0	15	0	11	1	0	0	0	0	0	1	27
20	52	W	2019	06-09-2019	9	1	South West	1	Isles of Scilly	A3112	Major	A3111	A3110	91203	10217	49.912239	-6.302848	2.0	1.24	10	3	20	1	16	1	0	0	0	0	0	1	41
21	52	E	2019	06-09-2019	15	1	South West	1	Isles of Scilly	A3112	Major	A3111	A3110	91203	10217	49.912239	-6.302848	2.0	1.24	29	1	33	0	11	0	0	0	0	0	0	0	45
22	52	W	2019	06-09-2019	11	1	South West	1	Isles of Scilly	A3112	Major	A3111	A3110	91203	10217	49.912239	-6.302848	2.0	1.24	3	2	7	1	21	0	0	0	0	0	0	0	31
23	52	E	2019	06-09-2019	16	1	South West	1	Isles of Scilly	A3112	Major	A3111	A3110	91203	10217	49.912239	-6.302848	2.0	1.24	9	1	13	0	7	0	0	0	0	0	0	0	21
24	6016	W	2019	02-10-2019	14	1	South West	68	Wiltshire	M4	Major	LA Boundary	17	389200	179080	51.510542	-2.157015	10.0	6.21	0	5	1522	7	554	44	19	11	18	67	95	254	2342
25	6016	E	2019	02-10-2019	10	1	South West	68	Wiltshire	M4	Major	LA Boundary	17	389200	179080	51.510542	-2.157015	10.0	6.21	0	5	1124	14	351	52	12	15	20	90	84	273	1767
26	6016	W	2019	02-10-2019	9	1	South West	68	Wiltshire	M4	Major	LA Boundary	17	389200	179080	51.510542	-2.157015	10.0	6.21	0	12	1352	11	336	19	10	11	10	85	43	178	1889
27	6016	E	2019	02-10-2019	7	1	South West	68	Wiltshire	M4	Major	LA Boundary	17	389200	179080	51.510542	-2.157015	10.0	6.21	0	9	2284	11	701	55	13	20	9	72	58	227	3232
28	6016	E	2019	02-10-2019	9	1	South West	68	Wiltshire	M4	Major	LA Boundary	17	389200	179080	51.510542	-2.157015	10.0	6.21	0	5	1434	14	411	47	10	10	11	86	84	248	2112
29	6016	E	2019	02-10-2019	13	1	South West	68	Wiltshire	M4	Major	LA Boundary	17	389200	179080	51.510542	-2.157015	10.0	6.21	0	4	1337	10	366	48	7	14	14	102	77	262	1979
30	6016	W	2019	02-10-2019	15	1	South West	68	Wiltshire	M4	Major	LA Boundary	17	389200	179080	51.510542	-2.157015	10.0	6.21	0	5	1843	11	614	61	8	7	13	67	112	268	2741
31	6016	W	2019	02-10-2019	16	1	South West	68	Wiltshire	M4	Major	LA Boundary	17	389200	179080	51.510542	-2.157015	10.0	6.21	0	7	1468	8	446	22	4	14	6	29	52	127	2056
32	6016	E	2019	02-10-2019	16	1	South West	68	Wiltshire	M4	Major	LA Boundary	17	389200	179080	51.510542	-2.157015	10.0	6.21	0	6	1999	7	407	42	1	3	13	78	62	199	2618
33	6016	E	2019	02-10-2019	11	1	South West	68	Wiltshire	M4	Major	LA Boundary	17	389200	179080	51.510542	-2.157015	10.0	6.21	0	1	1248	10	333	36	7	9	15	82	94	243	1835
34	6016	W	2019	02-10-2019	10	1	South West	68	Wiltshire	M4	Major	LA Boundary	17	389200	179080	51.510542	-2.157015	10.0	6.21	0	1	1307	18	341	31	12	14	14	96	78	245	1912
35	6016	E	2019	02-10-2019	17	1	South West	68	Wiltshire	M4	Major	LA Boundary	17	389200	179080	51.510542	-2.157015	10.0	6.21	0	8	1981	14	286	23	2	2	12	85	74	198	2487
36	6016	E	2019	02-10-2019	18	1	South West	68	Wiltshire	M4	Major	LA Boundary	17	389200	179080	51.510542	-2.157015	10.0	6.21	0	4	1422	10	224	26	2	1	9	77	46	161	1821
37	6016	W	2019	02-10-2019	8	1	South West	68	Wiltshire	M4	Major	LA Boundary	17	389200	179080	51.510542	-2.157015	10.0	6.21	0	11	1657	1	349	29	5	9	6	78	80	207	2225
38	6016	E	2019	02-10-2019	8	1	South West	68	Wiltshire	M4	Major	LA Boundary	17	389200	179080	51.510542	-2.157015	10.0	6.21	0	8	2174	8	574	87	11	18	6	63	105	290	3054
39	6016	E	2019	02-10-2019	14	1	South West	68	Wiltshire	M4	Major	LA Boundary	17	389200	179080	51.510542	-2.157015	10.0	6.21	0	4	1403	20	407	50	6	13	9	128	74	280	2114
40	6016	E	2019	02-10-2019	15	1	South West	68	Wiltshire	M4	Major	LA Boundary	17	389200	179080	51.510542	-2.157015	10.0	6.21	0	9	1728	11	429	36	6	9	19	92	91	253	2430
41	6016	W	2019	02-10-2019	7	1	South West	68	Wiltshire	M4	Major	LA Boundary	17	389200	179080	51.510542	-2.157015	10.0	6.21	0	7	1961	1	481	40	9	5	6	79	96	235	2685
42	6016	E	2019	02-10-2019	12	1	South West	68	Wiltshire	M4	Major	LA Boundary	17	389200	179080	51.510542	-2.157015	10.0	6.21	0	1	1157	12	334	45	7	11	13	93	79	248	1752
43	6016	W	2019	02-10-2019	11	1	South West	68	Wiltshire	M4	Major	LA Boundary	17	389200	179080	51.510542	-2.157015	10.0	6.21	0	8	1254	3	378	27	14	8	13	74	76	212	1855
44	6016	W	2019	02-10-2019	12	1	South West	68	Wiltshire	M4	Major	LA Boundary	17	389200	179080	51.510542	-2.157015	10.0	6.21	0	7	1243	15	387	46	7	17	14	79	97	260	1912
45	6016	W	2019	02-10-2019	13	1	South West	68	Wiltshire	M4	Major	LA Boundary	17	389200	179080	51.510542	-2.157015	10.0	6.21	0	7	1247	9	448	46	8	20	15	45	88	222	1933
46	6016	W	2019	02-10-2019	17	1	South West	68	Wiltshire	M4	Major	LA Boundary	17	389200	179080	51.510542	-2.157015	10.0	6.21	0	12	1696	5	351	15	3	2	4	18	44	86	2150
47	6016	W	2019	02-10-2019	18	1	South West	68	Wiltshire	M4	Major	LA Boundary	17	389200	179080	51.510542	-2.157015	10.0	6.21	0	7	1613	7	249	14	3	3	8	22	23	73	1949
48	6019	S	2019	17-06-2019	10	1	South West	70	Gloucestershire	M5	Major	11	10	389660	223200	51.907239	-2.151710	4.7	2.92	0	2	2651	15	519	133	14	13	17	35	250	462	3649
49	6019	S	2019	17-06-2019	12	1	South West	70	Gloucestershire	M5	Major	11	10	389660	223200	51.907239	-2.151710	4.7	2.92	0	14	2548	16	423	107	14	17	23	21	198	380	3381

	count_point_id	hour	latitude	longitude	link_length_km	pedal_cycles	two_wheeled_motor_vehicles	cars_and_taxis	all_gvs
0	52	7	49.912239	-6.302848	4.0	7.0	1.0	14.0	19.0
1	52	8	49.912239	-6.302848	4.0	45.0	3.0	42.0	27.0
2	52	9	49.912239	-6.302848	4.0	14.0	3.0	39.0	36.0
3	52	10	49.912239	-6.302848	4.0	10.0	2.0	44.0	31.0
4	52	11	49.912239	-6.302848	4.0	11.0	4.0	21.0	39.0
...	...	...	...	...	...	...	...	...	...
251347	996768	14	57.146594	-2.088728	0.0	0.0	0.0	394.0	0.0
251348	996768	15	57.146594	-2.088728	0.0	0.0	0.0	213.0	0.0
251349	996768	16	57.146594	-2.088728	0.0	0.0	0.0	416.0	0.0
251350	996768	17	57.146594	-2.088728	0.0	0.0	0.0	332.0	0.0
251351	996768	18	57.146594	-2.088728	0.0	0.0	0.0	150.0	0.0

	Accident_Severity	Date	0
0	Fatal	2018-01-01	4.0
1	Fatal	2018-01-02	3.0
2	Fatal	2018-01-03	3.0
3	Fatal	2018-01-04	6.0
4	Fatal	2018-01-05	4.0
5	Fatal	2018-01-06	1.0
6	Fatal	2018-01-07	2.0
7	Fatal	2018-01-08	0.0
8	Fatal	2018-01-09	2.0
9	Fatal	2018-01-10	5.0
10	Fatal	2018-01-11	7.0
11	Fatal	2018-01-12	3.0
12	Fatal	2018-01-13	6.0
13	Fatal	2018-01-14	4.0
14	Fatal	2018-01-15	2.0
15	Fatal	2018-01-16	2.0
16	Fatal	2018-01-17	5.0
17	Fatal	2018-01-18	2.0
18	Fatal	2018-01-19	4.0
19	Fatal	2018-01-20	8.0
20	Fatal	2018-01-21	5.0
21	Fatal	2018-01-22	5.0
22	Fatal	2018-01-23	3.0
23	Fatal	2018-01-24	1.0
24	Fatal	2018-01-25	7.0
25	Fatal	2018-01-26	5.0
26	Fatal	2018-01-27	3.0
27	Fatal	2018-01-28	5.0
28	Fatal	2018-01-29	5.0
29	Fatal	2018-01-30	1.0
30	Fatal	2018-01-31	1.0
31	Fatal	2018-02-01	5.0
32	Fatal	2018-02-02	6.0
33	Fatal	2018-02-03	5.0
34	Fatal	2018-02-04	2.0
35	Fatal	2018-02-05	2.0
36	Fatal	2018-02-06	6.0
37	Fatal	2018-02-07	3.0
38	Fatal	2018-02-08	0.0
39	Fatal	2018-02-09	3.0

	count_point_id	latitude	longitude	pedal_cycles	two_wheeled_motor_vehicles	cars_and_taxis	buses_and_coaches	lgvs	all_hgvs
0	51	49.915021	-6.317073	244.0	96.5	545.5	25.0	450.0	43.5
1	52	49.912291	-6.304267	163.0	51.0	537.5	7.5	247.5	8.5
2	53	49.917139	-6.306114	184.5	88.5	795.0	7.0	427.5	21.5
3	54	49.917809	-6.298996	63.0	55.0	346.5	6.0	298.5	37.0
4	55	49.918583	-6.295093	34.5	20.5	167.5	0.0	179.5	16.0

	date	geography	geography code	Rural Urban	Variable: All usual residents; measures: Value	Variable: Males; measures: Value	Variable: Females; measures: Value	Variable: Lives in a household; measures: Value	Variable: Lives in a communal establishment; measures: Value	Variable: Schoolchild or full-time student aged 4 and over at their non term-time address; measures: Value	Variable: Area (Hectares); measures: Value	Variable: Density (number of persons per hectare); measures: Value
0	2011	Darlington 001B	E01012334	Total	2466	1220	1246	2466	0	42	6033.58	0.4
1	2011	Darlington 001C	E01012335	Total	1383	682	701	1383	0	21	114.01	12.1
2	2011	Darlington 001D	E01012366	Total	2008	972	1036	2008	0	30	6718.16	0.3
3	2011	Darlington 001E	E01033481	Total	1364	680	684	1350	14	21	146.95	9.3
4	2011	Darlington 001F	E01033482	Total	1621	810	811	1621	0	16	234.31	6.9

	LSOA	pop_density
0	E01012334	0.4
1	E01012335	12.1
2	E01012366	0.3
3	E01033481	9.3
4	E01033482	6.9